﻿using DbUpgradeOracle.General;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace DbUpgradeOracle
{
    public partial class FormMain : Form
    {
        private List<DataVersion> _list = new List<DataVersion>();

        private DataVersion _currentVersion = null;

        //private string _dbName = "";
        //private string _dbType = "";
        public string Version { get; set; }

        #region 更新脚本链表

        private object _lock_upDbs = new object();
        
        #endregion

        public string key = "MKN7HS3E";

        public FormMain()
        {
            InitializeComponent();

            AssemblyFileVersionAttribute vFile = (AssemblyFileVersionAttribute)AssemblyFileVersionAttribute.GetCustomAttribute(System.Reflection.Assembly.GetExecutingAssembly(),
            typeof(AssemblyFileVersionAttribute));
            string[] verArray = vFile.Version.Split('.');
            Version = "V" + verArray[0] + "." + verArray[1] + "." + verArray[2] + "." + verArray[3];

            menuAuto.Click += MenuAuto_Click;
            menuHand.Click += MenuHand_Click;
            menuTitle.Click += MenuTitle_Click;

        }

        private void FormMain_Load(object sender, EventArgs e)
        {
            this.AutoScaleMode = AutoScaleMode.Dpi;

            this.Text = $"数据库版本管理工具Oracle版({Version})";

            dataGridView1.AutoGenerateColumns = false;
            try
            {
                //Upgrade.Instance.UpDb();
                //ConfigManager config = new ConfigManager("config.json");
                //DbSet set = config.GetConfig<DbSet>("WebDbset");
                //_dbName = set.Db;
                //_dbType = set.DbType;

                //RefreshVersion();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void dataGridView1_RowEnter(object sender, DataGridViewCellEventArgs e)
        {
            int index = e.RowIndex;
            //DataVersion ver = _list[index];
            //edtSql.Text = ver.Content;
            //redtLog.AppendText(ver.Title+"\n");
            //redtLog.ScrollToCaret();
            if (index >= 0)
            {
                ShowRowSql(index);
            }
        }

        private void btnNew_Click(object sender, EventArgs e)
        {
            if (App.IsChooseDb)
            {
                FormNewVersion f = new FormNewVersion();
                if (f.ShowDialog(this) == DialogResult.OK)
                {
                    string sqlTitle = f.SqlTitle;
                    string itype = f.IType;
                    string sql = "select seq from sqlite_sequence where name = 'DbVersion' ";
                    int maxid = App.Dal.ExecScalar(sql).ToInt32Ex();
                    maxid++;
                    sql = string.Format("insert into DbVersion (Version,itype,title) values ({0},'{1}','{2}')", maxid, itype, sqlTitle);
                    App.Dal.ExecNoQuery(sql);

                    sql = "select Id from UpGradeSql where Version = " + maxid;
                    object obj = App.Dal.ExecScalar(sql);

                    string sqltext = string.Empty;
                    if (obj != null)
                    {
                        //string id = obj.ToStringEx();
                        //sql = string.Format("update UpGradeSql set Sql = '{0}' where Id = {1}", DES.Encode(sqltext, key), id);
                        //SqliteHelper.Instance.ExecNoQuery(sql); ;
                    }
                    else
                    {
                        sqltext = string.Format(@"/*==============================================================*/
/* 版本{0}：                                                                                                                              */
/*==============================================================*/", maxid);
                        sql = string.Format("insert into UpGradeSql (Version,Sql,itype) values ({0},'{1}','{2}')", maxid, DES.Encode(sqltext, key), itype);
                        App.Dal.ExecNoQuery(sql); ;
                    }

                    RefreshVersion();

                    //sql = "select sql from UpGradeSql where Version = " + maxid;
                    //sqltext = SqliteHelper.Instance.ExecScalar(sql).ToStringEx();
                    //edtSql.Text = DES.Decode(sqltext, key);
                }
            }
        }

        private void RefreshVersion()
        {
            Task.Run(() =>
            {
                if (App.IsChooseDb)
                {
                    string sql = "select * from DbVersion order by Version desc";
                    DataTable dt = App.Dal.ExecDataTable(sql);
                    lock (_lock_upDbs)
                    {
                        _list.Clear();
                        if (dt != null && dt.Rows.Count > 0)
                        {
                            foreach (DataRow dr in dt.Rows)
                            {
                                DataVersion ver = new DataVersion();
                                ver.Version = dr["Version"].ToInt32Ex();
                                ver.Category = dr["itype"].ToStringEx();
                                ver.Title = dr["title"].ToStringEx();
                                _list.Add(ver);
                            }
                        }
                        this.BeginInvoke(new Action(() => {
                            edtSql.Text = string.Empty;
                            redtLog.Clear();
                            dataGridView1.DataSource = null;
                            dataGridView1.DataSource = _list;
                        }));

                    }
                }
            });
        }

        private void ShowRowSql(int index)
        {
            DataVersion ver = _list[index];
            _currentVersion = ver;
            string sql = "select sql from UpGradeSql where Version = " + ver.Version;
            string sqltext = App.Dal.ExecScalar(sql).ToStringEx();
            edtSql.Text = DES.Decode(sqltext, key);
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            if (App.IsChooseDb)
            {
                string sqltext = edtSql.Text;

                if (_currentVersion == null)
                {
                    MessageBox.Show("没选中版本!");
                    return;
                }
                if (MessageBox.Show("确定保存脚本吗?", "", MessageBoxButtons.YesNo) == System.Windows.Forms.DialogResult.Yes)
                {
                    string sql = "select Id from UpGradeSql where Version = " + _currentVersion.Version;
                    object obj = App.Dal.ExecScalar(sql);
                    if (obj != null)
                    {
                        string id = obj.ToStringEx();
                        sql = string.Format("update UpGradeSql set Sql = '{0}' where Id = {1}", DES.Encode(sqltext, key), id);
                        App.Dal.ExecNoQuery(sql); ;
                    }
                    else
                    {
                        sql = string.Format("insert into UpGradeSql (Version,Sql,itype) values ({0},'{1}','自动')", _currentVersion.Version, DES.Encode(sqltext, key));
                        App.Dal.ExecNoQuery(sql); ;
                    }
                    MessageBox.Show("更新完成!");
                }
            }
        }

        private void btnDemo_Click(object sender, EventArgs e)
        {
            FormExample f = new FormExample();
            f.ShowDialog(this);
        }

        private void MenuTitle_Click(object sender, EventArgs e)
        {
            if (App.IsChooseDb)
            {
                int index = dataGridView1.CurrentRow.Index;
                DataVersion ver = _list[index];
                FormEditVersion f = new FormEditVersion(ver.Title);
                if (f.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                {
                    string sql = "update DbVersion set title = '" + f.SqlTitle + "' where Version = " + ver.Version;
                    App.Dal.ExecNoQuery(sql);
                    RefreshVersion();
                }
            }
        }

        private void MenuHand_Click(object sender, EventArgs e)
        {
            if (App.IsChooseDb)
            {
                int index = dataGridView1.CurrentRow.Index;
                DataVersion ver = _list[index];
                if (MessageBox.Show(string.Format("确定要将" + ver.Version + " 改为手动吗?"), "", MessageBoxButtons.YesNo) == System.Windows.Forms.DialogResult.Yes)
                {
                    string sql = "update DbVersion set itype = '手动' where Version = " + ver.Version;
                    App.Dal.ExecNoQuery(sql);
                    RefreshVersion();
                }
            }
        }

        private void MenuAuto_Click(object sender, EventArgs e)
        {
            if (App.IsChooseDb)
            {
                int index = dataGridView1.CurrentRow.Index;
                DataVersion ver = _list[index];
                if (MessageBox.Show(string.Format("确定要将" + ver.Version + " 改为自动吗?"), "", MessageBoxButtons.YesNo) == System.Windows.Forms.DialogResult.Yes)
                {
                    string sql = "update DbVersion set itype = '自动' where Version = " + ver.Version;
                    App.Dal.ExecNoQuery(sql);
                    RefreshVersion();
                }
            }
        }

        private void btnUpgrade_Click(object sender, EventArgs e)
        {
            if (App.IsChooseDb)
            {
                if (MessageBox.Show(string.Format("数据库【{0}】确定要升级数据库吗?", App.ChooseDb.DbName), "", MessageBoxButtons.YesNo) == System.Windows.Forms.DialogResult.Yes)
                {
                    string sql = string.Format("select count(*) from dba_tables where owner = '{0}' and table_name = '{1}'", App.ChooseDb.UserName.ToUpper(), "DBVERSION");
                    try
                    {
                        int icount = OracleHelper.Instance.ExecScalar(sql).ToInt32Ex();
                        if (icount <= 0)
                        {
                            sql = @"create table DBVERSION 
                        (
                           ID                   INTEGER              not null,
                           VERSION              INTEGER              not null,
                           DBTYPE               VARCHAR(600)              null,
                           constraint PK_DBVERSION primary key (ID)
                        );

                        comment on table DBVERSION is
                        '数据库版本';

                        comment on column DBVERSION.ID is
                        '主键';

                        comment on column DBVERSION.VERSION is
                        '版本';

                        comment on column DBVERSION.DBTYPE is
                        '数据库类型';
                        ";
                            OracleHelper.Instance.BeginTran();
                            OracleHelper.Instance.ExecNoQuerys(sql);
                            sql = string.Format("insert into DBVERSION (ID,VERSION,DBTYPE) values (1,0,'{0}')", App.ChooseDb.DbCheckName);
                            OracleHelper.Instance.ExecNoQuery(sql);
                            OracleHelper.Instance.Commit();
                        }
                        else
                        {
                            sql = string.Format("select count(*) from dba_tab_columns where owner = '{0}' and table_name = 'DBVERSION' and COLUMN_NAME = 'DBTYPE'", App.ChooseDb.UserName.ToUpper());
                            int col1Count = OracleHelper.Instance.ExecScalar(sql).ToInt32Ex();
                            if (col1Count <= 0)
                            {
                                sql = @"alter table DBVERSION add(DBTYPE  VARCHAR(600) null);

                                    comment on column DBVERSION.DBTYPE is '数据库类型';";
                                OracleHelper.Instance.BeginTran();
                                OracleHelper.Instance.ExecNoQuerys(sql);
                                sql = string.Format("update DBVERSION set DBTYPE ='{0}' where id = 1", App.ChooseDb.DbCheckName);
                                OracleHelper.Instance.ExecNoQuery(sql);
                                OracleHelper.Instance.Commit();
                            }
                            else//字段全
                            {
                                sql = "select DBTYPE from DBVERSION";
                                string idbtype = OracleHelper.Instance.ExecScalar(sql).ToStringEx();
                                if (idbtype != App.ChooseDb.DbCheckName)
                                {
                                    MessageBox.Show("本升级脚本不使用该数据库！");
                                    return;
                                }
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        OracleHelper.Instance.Rollback();
                        AddHint(ex.Message);
                    }

                    sql = "select VERSION from DBVERSION";
                    int version = OracleHelper.Instance.ExecScalar(sql).ToInt32Ex();

                    sql = "select * from DbVersion order by Version";
                    DataTable dt = App.Dal.ExecDataTable(sql);
                    List<Ver> vlist = new List<Ver>();

                    if (dt != null && dt.Rows.Count > 0)
                    {
                        foreach (DataRow dr in dt.Rows)
                        {
                            int slversion = dr["Version"].ToInt32Ex();
                            string itype = dr["iType"].ToStringEx();
                            if (slversion > version)//版本需要更新
                            {
                                if (itype == "手动")
                                {
                                    Ver v = new Ver();
                                    v.Id = slversion;
                                    v.IType = "手动";
                                    vlist.Add(v);
                                    break;
                                }
                                else
                                {
                                    Ver v = new Ver();
                                    v.Id = slversion;
                                    v.IType = "自动";
                                    vlist.Add(v);
                                }
                            }
                        }
                    }

                    redtLog.Text = "";

                    try
                    {
                        OracleHelper.Instance.BeginTran();
                        foreach (Ver item in vlist)
                        {
                            if (item.IType == "手动")
                            {
                                //UpgradeDatabase(item.Id);
                                FormHandExecSql f = new FormHandExecSql();
                                f.Ini(item.Id);
                                if (f.ShowDialog(this) == System.Windows.Forms.DialogResult.OK)
                                {
                                    UpgradeDatabaseHand(item.Id);
                                }
                            }
                            else//自动
                            {
                                UpgradeDatabase(item.Id);
                            }
                        }
                        OracleHelper.Instance.Commit();
                        AddHint("更新完成----------------------");
                    }
                    catch (Exception ex)
                    {
                        AddHint("更新失败，已回滚----------------------");
                        AddHint(ex.Message);
                        OracleHelper.Instance.Rollback();
                    }
                }
            }
        }

        private void AddHint(string text)
        {
            redtLog.AppendText(text + "\n");
            redtLog.ScrollToCaret();
        }

        private bool UpgradeDatabase(int version)
        {
            if (App.IsChooseDb)
            {
                AddHint(string.Format("更新版本----------------------{0}", version));
                string sql = "select sql from UpGradeSql where Version = " + version;
                string sqltext = App.Dal.ExecScalar(sql).ToStringEx();
                sqltext = DES.Decode(sqltext, key);
                if (!string.IsNullOrEmpty(sqltext))
                {
                    OracleHelper.Instance.ExecNoQuerys(sqltext);

                    sql = "update DBVERSION set VERSION = " + version;
                    OracleHelper.Instance.ExecNoQuery(sql);
                    return true;
                }
            }
            return false;
        }

        private bool UpgradeDatabaseHand(int version)
        {
            AddHint(string.Format("更新版本----------------------{0}", version));
            //string sql = "select sql from UpGradeSql where Version = " + version;
            //string sqltext = SqliteHelper.Instance.ExecScalar(sql).ToStringEx();
            //sqltext = DES.Decode(sqltext, key);

            string sql = "update DBVERSION set VERSION = " + version;
            OracleHelper.Instance.ExecNoQuery(sql);
            return true;
        }

        private void btnChooseDb_Click(object sender, EventArgs e)
        {
            FormChooseDb form = new FormChooseDb();
            if (form.ShowDialog() == DialogResult.OK)
            {
                //刷新选择数据库
                App.ChooseDb = form.ChooseDbinfo;
                _currentVersion = null;
                lblHint.Text = $"当前管理库=>《{App.ChooseDb.DbChineseName}》({App.ChooseDb.DbCheckName}) ip：{App.ChooseDb.Ip}，服务名：{App.ChooseDb.DbName}，用户名：{App.ChooseDb.UserName}，密码：{App.ChooseDb.Password}";
                RefreshVersion();
            }
        }

        private void dataGridView1_RowPrePaint(object sender, DataGridViewRowPrePaintEventArgs e)
        {
            DataGridView grid = (DataGridView)sender;
            int index = e.RowIndex;
            string value = grid.Rows[index].Cells[1].Value.ToString();
            if (value == "手动")
            {
                int cellCount = grid.Rows[index].Cells.Count;
                for (int i = 0; i < cellCount; i++)
                {
                    grid.Rows[index].Cells[i].Style.ForeColor = System.Drawing.Color.Red;
                }
            }
            else
            {
                int cellCount = grid.Rows[index].Cells.Count;
                for (int i = 0; i < cellCount; i++)
                {
                    grid.Rows[index].Cells[i].Style.ForeColor = System.Drawing.Color.Green;
                }
            }
        }
    }
}
